/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Modele;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashSet;
import java.util.TreeSet;

/**
 *
 * @author sio
 */
public class DAO {

    public static Collection<Dep> getLesDeps() {
        Collection<Dep> maCol = new TreeSet<Dep>();

        try {
            Connection con = Connect.get();
            Statement req;

            req = con.createStatement();

            ResultSet rs = req.executeQuery("select distinct departement from medecin");
            while (rs.next()) {
                Dep d = new Dep(rs.getString("departement"));
                maCol.add(d);
            }
        } catch (SQLException e) {
            System.out.println("Erreur SQL :" + e);
        }
        return maCol;
    }

    public static Collection<Spe> getLesSpes() {
        Collection<Spe> maCol = new TreeSet<Spe>();

        try {
            Connection con = Connect.get();
            Statement req;
            Statement req2;
            req = con.createStatement();
            req2 = con.createStatement();
            ResultSet rs = req.executeQuery("select distinct specialitecomplementaire from medecin");
            while (rs.next()) {
                if (rs.getString("specialitecomplementaire") != null) {
                    Collection<Med> maColMed = new TreeSet<Med>();
                    ResultSet rs2 = req2.executeQuery("select * from medecin where specialitecomplementaire='"
                            + rs.getString("specialitecomplementaire") + "'");
                    while (rs2.next()) {
                        Med m = new Med(rs2.getString("nom"), rs2.getString("prenom"), rs2.getString("adresse"), rs2.getString("tel"), rs2.getString("specialitecomplementaire"), rs2.getString("id"), rs2.getString("departement"));
                        maColMed.add(m);
                    }

                    Spe s = new Spe(rs.getString("specialitecomplementaire"), maColMed);
                    maCol.add(s);
                }
            }
        } catch (SQLException e) {
            System.out.println("Erreur SQL :" + e);
        }
        return maCol;
    }

    public static Collection<Med> getLesMeds() {
        Collection<Med> maCol = new TreeSet<Med>();

        try {
            Connection con = Connect.get();
            Statement req;
            req = con.createStatement();

            ResultSet rs = req.executeQuery("select * from medecin");
            while (rs.next()) {
                Med m = new Med(rs.getString("nom"), rs.getString("prenom"), rs.getString("adresse"), rs.getString("tel"), rs.getString("specialitecomplementaire"), rs.getString("id"), rs.getString("departement"));
                maCol.add(m);
            }
        } catch (SQLException e) {
            System.out.println("Erreur SQL :" + e);
        }
        return maCol;
    }

}
